﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace Factory
{
    class ChequeDAO
    {

        public static void insereCheque(Cheque c)
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"insert into Cheque (documentoEmissor, documentoCliente, valor,idStatus,dataConsulta,resultadoConsulta, datacompensacao, idBanco) values "
                    + "('" + c.documentoEmissor + "','" + c.documentoCliente + "'," + c.valor + ",1, '" + DateTime.Today + "',0, '" + c.dataCompensacao + "'," + c.IdBanco + ")";
                #endregion

                MessageBox.Show(query);

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                SqlDataReader dr = cmd.ExecuteReader();

                // IList<Cliente> clientes = ReaderToObjects(dr);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        public IList<Cheque> ObterChequesNaoCompensados()
        {
            try
            {
                DAOBase m_cnn = new DAOBase();
                #region Query
                string query =
                @"SELECT 
ID
,DocumentoCliente
,DocumentoEmissor
,IdBanco
,Valor
,IdStatus
,DataConsulta
,ResultadoConsulta
,DataCompensacao
FROM Cheque WHERE IdStatus = 0";
                #endregion
                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());
                SqlDataReader dr = cmd.ExecuteReader();
                IList<Cheque> cheques = ReaderToObjects(dr);
                return cheques;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public static IList<Cheque> ReaderToObjects(IDataReader reader)
        {
            if (reader.IsClosed)
            {
                throw new ArgumentException("O DataReader fornecido já estava fechado.");
            }

            int Id = reader.GetOrdinal("ID");
            int DocumentoCliente = reader.GetOrdinal("DocumentoCliente");
            int DocumentoEmissor = reader.GetOrdinal("DocumentoEmissor");
            int IdBanco = reader.GetOrdinal("IdBanco");
            int Valor = reader.GetOrdinal("Valor");
            int IdStatus = reader.GetOrdinal("IdStatus");
            int DataConsulta = reader.GetOrdinal("DataConsulta");
            int ResultadoConsulta = reader.GetOrdinal("ResultadoConsulta");
            int DataCompensacao = reader.GetOrdinal("DataCompensacao");

            IList<Cheque> result = new List<Cheque>();

            while (reader.Read())
            {
                Cheque c = new Cheque()
                {
                    dataCompensacao = reader.GetDateTime(DataCompensacao),
                    dataConsulta = reader.GetDateTime(DataConsulta),
                    IdBanco = reader.GetInt32(IdBanco),
                    valor = reader.GetDouble(Valor),
                    status = reader.GetInt32(IdStatus),
                    ResultadoConsulta = reader.GetInt32(ResultadoConsulta),
                    id = reader.GetInt32(Id),
                    documentoCliente = reader.GetString(DocumentoCliente),
                    documentoEmissor = reader.GetString(DocumentoEmissor),

                };

                result.Add(c);
            }
            return result;
        }

        public IList<Cheque> ObterChequePorDocumentoCliente(string documento)
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"SELECT 
                       ID
                      ,DocumentoCliente
                      ,DocumentoEmissor
                      ,IdBanco
                      ,Valor
                      ,IdStatus
                      ,DataConsulta
                      ,ResultadoConsulta
                      ,DataCompensacao
                  FROM Cheque WHERE DocumentoCliente = @id";
                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());
                cmd.Parameters.Add(new SqlParameter("id", documento));
                SqlDataReader dr = cmd.ExecuteReader();

                IList<Cheque> cheques = ReaderToObjects(dr);

                return cheques;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public IList<Cheque> ObterCheques()
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"SELECT 
                       ID
                      ,DocumentoCliente
                      ,DocumentoEmissor
                      ,IdBanco
                      ,Valor
                      ,IdStatus
                      ,DataConsulta
                      ,ResultadoConsulta
                      ,DataCompensacao
                  FROM Cheque";
                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                SqlDataReader dr = cmd.ExecuteReader();

                IList<Cheque> cheques = ReaderToObjects(dr);

                return cheques;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public int ExcluirCheque(int idCheque)
        {
            int resultado = 0;

            try
            {
                DAOBase m_cnn = new DAOBase();

                #region query
                string query = @"UPDATE CHEQUE SET IDSTATUS=1
                                 WHERE ID=@ID";

                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());
                cmd.Parameters.Add(new SqlParameter("ID", idCheque));

                resultado = cmd.ExecuteNonQuery();

                m_cnn.DestroyConnection();

            }

            catch (Exception ex)
            {

            }

            return resultado;
        }

        public int cadastrarCheque(Cheque cheque)
        {
            int resultado = 0;

            try
            {

                DAOBase m_cnn = new DAOBase();

                #region query
                string query = @"INSERT INTO Cheque
       
                             VALUES
                                   (@DocumentoCliente
                                   ,@DocumentoEmissor
                                   ,@IdBanco
                                   ,@Valor
                                   ,@IdStatus
                                   ,@DataConsulta
                                   ,@ResultadoConsulta
                                   ,@DataCompensacao
                                   ,@ValorPago)";

                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                //Parametros de Usuario
                cmd.Parameters.Add(new SqlParameter("DocumentoCliente", cheque.documentoCliente));
                cmd.Parameters.Add(new SqlParameter("DocumentoEmissor", cheque.documentoEmissor));
                cmd.Parameters.Add(new SqlParameter("IdBanco", cheque.IdBanco));
                cmd.Parameters.Add(new SqlParameter("Valor", cheque.valor));
                cmd.Parameters.Add(new SqlParameter("IdStatus", cheque.status));
                cmd.Parameters.Add(new SqlParameter("DataConsulta", cheque.dataConsulta));
                cmd.Parameters.Add(new SqlParameter("ResultadoConsulta", cheque.ResultadoConsulta));
                cmd.Parameters.Add(new SqlParameter("DataCompensacao", cheque.dataCompensacao));
                cmd.Parameters.Add(new SqlParameter("ValorPago", cheque.valorPago));

                resultado = cmd.ExecuteNonQuery();

                m_cnn.DestroyConnection();
            }

            catch (Exception ex)
            {
                //writer.WriteLine("ApplicationException Message: {0}", ex.Message);
            }

            return resultado;
        }



        public Cheque ObterChequePorID(int idCheque)
        {
            try
            {
                DAOBase m_cnn = new DAOBase();

                #region Query
                string query =
                    @"SELECT 
                       ID
                      ,DocumentoCliente
                      ,DocumentoEmissor
                      ,IdBanco
                      ,Valor
                      ,IdStatus
                      ,DataConsulta
                      ,ResultadoConsulta
                      ,DataCompensacao
                  FROM Cheque WHERE ID = @id";
                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());
                cmd.Parameters.Add(new SqlParameter("id", idCheque));
                SqlDataReader dr = cmd.ExecuteReader();

                IList<Cheque> cheques = ReaderToObjects(dr);

                return cheques.FirstOrDefault();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        public int atualizarCheque(int idCheque)
        {
            int resultado = 0;

            try
            {
                DAOBase m_cnn = new DAOBase();

                #region query
                string query = @"update cheque set idStatus = 1 where id = @idCheque";

                #endregion

                SqlCommand cmd = new SqlCommand(query, m_cnn.CreateConnection());

                //Parametros de Usuario
                cmd.Parameters.Add(new SqlParameter("idCheque", idCheque));

                resultado = cmd.ExecuteNonQuery();

                m_cnn.DestroyConnection();

            }

            catch (Exception ex)
            {

            }

            return resultado;
        }
    }
}
